-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_Insert]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_Update]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_Delete]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_Load]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_ACTP_Employee_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Employee_SelectAll]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_Insert]
	@ModuleID uniqueidentifier,
	@MembershipID int,
	@EmployeeCode varchar(128),
	@FirstName nvarchar(128),
	@LastName nvarchar(128),
	@PersonalTitle nvarchar(128),
	@JobTitle nvarchar(128),
	@Gender bit,
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime,
	@ID int OUTPUT
AS

INSERT INTO [dbo].[t_ACTP_Employees]
(
	[ModuleID],
	[MembershipID],
	[EmployeeCode],
	[FirstName],
	[LastName],
	[PersonalTitle],
	[JobTitle],
	[Gender],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
)
VALUES 
(
	@ModuleID,
	@MembershipID,
	@EmployeeCode,
	@FirstName,
	@LastName,
	@PersonalTitle,
	@JobTitle,
	@Gender,
	@CreatedBy,
	@CreatedDate,
	@UpdatedBy,
	@UpdatedDate
)

SET @ID = SCOPE_IDENTITY()

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_Update]
	@ID int,
	@ModuleID uniqueidentifier,
	@MembershipID int,
	@EmployeeCode varchar(128),
	@FirstName nvarchar(128),
	@LastName nvarchar(128),
	@PersonalTitle nvarchar(128),
	@JobTitle nvarchar(128),
	@Gender bit,
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime
AS

UPDATE
	[dbo].[t_ACTP_Employees]
SET
	[ModuleID] = @ModuleID,
	[MembershipID] = @MembershipID,
	[EmployeeCode] = @EmployeeCode,
	[FirstName] = @FirstName,
	[LastName] = @LastName,
	[PersonalTitle] = @PersonalTitle,
	[JobTitle] = @JobTitle,
	[Gender] = @Gender,
	[CreatedBy] = @CreatedBy,
	[CreatedDate] = @CreatedDate,
	[UpdatedBy] = @UpdatedBy,
	[UpdatedDate] = @UpdatedDate
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_InsertUpdate]
	@ID int,
	@ModuleID uniqueidentifier,
	@MembershipID int,
	@EmployeeCode varchar(128),
	@FirstName nvarchar(128),
	@LastName nvarchar(128),
	@PersonalTitle nvarchar(128),
	@JobTitle nvarchar(128),
	@Gender bit,
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_ACTP_Employees] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_ACTP_Employees] 
		SET
			[ModuleID] = @ModuleID,
			[MembershipID] = @MembershipID,
			[EmployeeCode] = @EmployeeCode,
			[FirstName] = @FirstName,
			[LastName] = @LastName,
			[PersonalTitle] = @PersonalTitle,
			[JobTitle] = @JobTitle,
			[Gender] = @Gender,
			[CreatedBy] = @CreatedBy,
			[CreatedDate] = @CreatedDate,
			[UpdatedBy] = @UpdatedBy,
			[UpdatedDate] = @UpdatedDate
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_ACTP_Employees]
		(
			[ModuleID],
			[MembershipID],
			[EmployeeCode],
			[FirstName],
			[LastName],
			[PersonalTitle],
			[JobTitle],
			[Gender],
			[CreatedBy],
			[CreatedDate],
			[UpdatedBy],
			[UpdatedDate]
		)
		VALUES 
		(
			@ModuleID,
			@MembershipID,
			@EmployeeCode,
			@FirstName,
			@LastName,
			@PersonalTitle,
			@JobTitle,
			@Gender,
			@CreatedBy,
			@CreatedDate,
			@UpdatedBy,
			@UpdatedDate
		)		
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_Delete]
	@ID int
AS

DELETE FROM 
	[dbo].[t_ACTP_Employees]
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_ACTP_Employees] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_Load]
	@ID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[MembershipID],
	[EmployeeCode],
	[FirstName],
	[LastName],
	[PersonalTitle],
	[JobTitle],
	[Gender],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_Employees]
WHERE
	[ID] = @ID
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[ID],
	[ModuleID],
	[MembershipID],
	[EmployeeCode],
	[FirstName],
	[LastName],
	[PersonalTitle],
	[JobTitle],
	[Gender],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM [dbo].[t_ACTP_Employees] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Employee_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Employee_SelectAll]
	@ModuleID UNIQUEIDENTIFIER
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[MembershipID],
	[EmployeeCode],
	[FirstName],
	[LastName],
	[PersonalTitle],
	[JobTitle],
	[Gender],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_Employees]	
WHERE 
	(ModuleID = @ModuleID)

GO

